# init_db.py

import sqlite3

def init_db():
    """创建数据库文件，创建药品信息表、入库记录表和出库记录表"""
    connection = sqlite3.connect('inventory.db')
    create_tables(connection)
    connection.close()

def create_tables(connection):
    """在数据库中创建所需的数据表
    
    Args:
        connection: SQLite数据库连接对象
    """
    cursor = connection.cursor()

    # 创建药品信息表
    cursor.execute('''CREATE TABLE IF NOT EXISTS drug_info (
                          drug_id INTEGER PRIMARY KEY AUTOINCREMENT,
                          name TEXT NOT NULL,
                          total_quantity INTEGER NOT NULL
                      );''')

    # 创建入库记录表
    cursor.execute('''CREATE TABLE IF NOT EXISTS stock_in (
                          record_id INTEGER PRIMARY KEY AUTOINCREMENT,
                          drug_id INTEGER NOT NULL,
                          quantity INTEGER NOT NULL,
                          purchase_date TEXT NOT NULL,
                          supplier TEXT NOT NULL,
                          FOREIGN KEY(drug_id) REFERENCES drug_info(drug_id)
                      );''')

    # 创建出库记录表
    cursor.execute('''CREATE TABLE IF NOT EXISTS stock_out (
                          record_id INTEGER PRIMARY KEY AUTOINCREMENT,
                          drug_id INTEGER NOT NULL,
                          quantity INTEGER NOT NULL,
                          usage_date TEXT NOT NULL,
                          department TEXT NOT NULL,
                          person TEXT NOT NULL,
                          FOREIGN KEY(drug_id) REFERENCES drug_info(drug_id)
                      );''')

    connection.commit()
    cursor.close()

if __name__ == '__main__':
    init_db()
